-- 店铺销售风险分析数据库设计

-- 1. 店铺基础信息表
CREATE TABLE Shop_Info (
    shop_id INT PRIMARY KEY IDENTITY(1,1),
    shop_name NVARCHAR(100) NOT NULL,
    shop_code NVARCHAR(50) UNIQUE NOT NULL,
    region NVARCHAR(50),
    city NVARCHAR(50),
    shop_type NVARCHAR(30), -- 直营店/加盟店/专柜等
    open_date DATE,
    shop_status NVARCHAR(20) DEFAULT 'ACTIVE', -- ACTIVE/INACTIVE/CLOSED
    created_date DATETIME DEFAULT GETDATE(),
    updated_date DATETIME DEFAULT GETDATE()
);

-- 2. 销售数据表
CREATE TABLE Sales_Data (
    id INT PRIMARY KEY IDENTITY(1,1),
    shop_id INT FOREIGN KEY REFERENCES Shop_Info(shop_id),
    sale_date DATE NOT NULL,
    total_amount DECIMAL(15,2),
    total_quantity INT,
    customer_count INT,
    return_amount DECIMAL(15,2) DEFAULT 0,
    return_quantity INT DEFAULT 0,
    created_date DATETIME DEFAULT GETDATE()
);

-- 3. 风险指标表
CREATE TABLE Risk_Indicators (
    indicator_id INT PRIMARY KEY IDENTITY(1,1),
    indicator_name NVARCHAR(100) NOT NULL,
    indicator_code NVARCHAR(50) UNIQUE NOT NULL,
    description NVARCHAR(500),
    risk_level NVARCHAR(20), -- HIGH/MEDIUM/LOW
    threshold_value DECIMAL(10,4),
    weight DECIMAL(3,2) DEFAULT 1.0,
    is_active BIT DEFAULT 1,
    created_date DATETIME DEFAULT GETDATE()
);

-- 4. 店铺风险评分表
CREATE TABLE Shop_Risk_Score (
    id INT PRIMARY KEY IDENTITY(1,1),
    shop_id INT FOREIGN KEY REFERENCES Shop_Info(shop_id),
    analysis_date DATE NOT NULL,
    total_risk_score DECIMAL(5,2),
    risk_level NVARCHAR(20), -- HIGH/MEDIUM/LOW
    risk_factors NVARCHAR(MAX), -- JSON格式存储具体风险因素
    created_date DATETIME DEFAULT GETDATE()
);

-- 5. 风险预警记录表
CREATE TABLE Risk_Alert (
    alert_id INT PRIMARY KEY IDENTITY(1,1),
    shop_id INT FOREIGN KEY REFERENCES Shop_Info(shop_id),
    alert_type NVARCHAR(50),
    alert_level NVARCHAR(20), -- CRITICAL/HIGH/MEDIUM/LOW
    alert_message NVARCHAR(500),
    alert_date DATETIME DEFAULT GETDATE(),
    is_resolved BIT DEFAULT 0,
    resolved_date DATETIME,
    resolved_by NVARCHAR(50)
);

-- 插入基础风险指标数据
INSERT INTO Risk_Indicators (indicator_name, indicator_code, description, risk_level, threshold_value, weight) VALUES
('销售下滑率', 'SALES_DECLINE_RATE', '连续期间销售金额下降百分比', 'HIGH', 0.15, 0.25),
('退货率', 'RETURN_RATE', '退货金额占销售总额比例', 'HIGH', 0.10, 0.20),
('客单价异常', 'AVG_ORDER_VALUE_ANOMALY', '客单价与历史平均值的偏差', 'MEDIUM', 0.30, 0.15),
('库存周转率', 'INVENTORY_TURNOVER', '库存周转天数异常', 'MEDIUM', 60.0, 0.15),
('客户流失率', 'CUSTOMER_CHURN_RATE', '老客户流失比例', 'HIGH', 0.20, 0.25);

-- 创建索引
CREATE INDEX IX_Sales_Data_Shop_Date ON Sales_Data(shop_id, sale_date);
CREATE INDEX IX_Shop_Risk_Score_Shop_Date ON Shop_Risk_Score(shop_id, analysis_date);
CREATE INDEX IX_Risk_Alert_Shop_Date ON Risk_Alert(shop_id, alert_date);

-- 店铺销售风险分析存储过程

-- 1. 计算销售下滑率
CREATE PROCEDURE sp_CalculateSalesDeclineRate
    @shop_id INT,
    @analysis_date DATE,
    @period_days INT = 30
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @current_period_start DATE = DATEADD(DAY, -@period_days, @analysis_date);
    DECLARE @previous_period_start DATE = DATEADD(DAY, -@period_days*2, @analysis_date);
    DECLARE @previous_period_end DATE = DATEADD(DAY, -@period_days, @analysis_date);
    
    DECLARE @current_sales DECIMAL(15,2), @previous_sales DECIMAL(15,2);
    
    -- 获取当前期间销售额
    SELECT @current_sales = ISNULL(SUM(total_amount), 0)
    FROM Sales_Data 
    WHERE shop_id = @shop_id 
    AND sale_date >= @current_period_start 
    AND sale_date <= @analysis_date;
    
    -- 获取上一期间销售额
    SELECT @previous_sales = ISNULL(SUM(total_amount), 0)
    FROM Sales_Data 
    WHERE shop_id = @shop_id 
    AND sale_date >= @previous_period_start 
    AND sale_date < @previous_period_end;
    
    -- 计算下滑率
    DECLARE @decline_rate DECIMAL(5,4);
    IF @previous_sales > 0
        SET @decline_rate = (@previous_sales - @current_sales) / @previous_sales;
    ELSE
        SET @decline_rate = 0;
    
    SELECT @decline_rate AS decline_rate, @current_sales AS current_sales, @previous_sales AS previous_sales;
END;

-- 2. 计算退货率
CREATE PROCEDURE sp_CalculateReturnRate
    @shop_id INT,
    @analysis_date DATE,
    @period_days INT = 30
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @period_start DATE = DATEADD(DAY, -@period_days, @analysis_date);
    
    SELECT 
        ISNULL(SUM(return_amount), 0) AS total_returns,
        ISNULL(SUM(total_amount), 0) AS total_sales,
        CASE 
            WHEN ISNULL(SUM(total_amount), 0) > 0 
            THEN ISNULL(SUM(return_amount), 0) / ISNULL(SUM(total_amount), 0)
            ELSE 0 
        END AS return_rate
    FROM Sales_Data 
    WHERE shop_id = @shop_id 
    AND sale_date >= @period_start 
    AND sale_date <= @analysis_date;
END;

-- 3. 计算客单价异常
CREATE PROCEDURE sp_CalculateAvgOrderValueAnomaly
    @shop_id INT,
    @analysis_date DATE,
    @period_days INT = 30
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @period_start DATE = DATEADD(DAY, -@period_days, @analysis_date);
    DECLARE @current_avg DECIMAL(10,2), @historical_avg DECIMAL(10,2);
    
    -- 当前期间平均客单价
    SELECT @current_avg = CASE 
        WHEN ISNULL(SUM(customer_count), 0) > 0 
        THEN ISNULL(SUM(total_amount), 0) / ISNULL(SUM(customer_count), 0)
        ELSE 0 
    END
    FROM Sales_Data 
    WHERE shop_id = @shop_id 
    AND sale_date >= @period_start 
    AND sale_date <= @analysis_date;
    
    -- 历史平均客单价（过去90天）
    SELECT @historical_avg = CASE 
        WHEN ISNULL(SUM(customer_count), 0) > 0 
        THEN ISNULL(SUM(total_amount), 0) / ISNULL(SUM(customer_count), 0)
        ELSE 0 
    END
    FROM Sales_Data 
    WHERE shop_id = @shop_id 
    AND sale_date >= DATEADD(DAY, -90, @analysis_date)
    AND sale_date < @period_start;
    
    -- 计算异常程度
    DECLARE @anomaly_rate DECIMAL(5,4);
    IF @historical_avg > 0
        SET @anomaly_rate = ABS(@current_avg - @historical_avg) / @historical_avg;
    ELSE
        SET @anomaly_rate = 0;
    
    SELECT @anomaly_rate AS anomaly_rate, @current_avg AS current_avg, @historical_avg AS historical_avg;
END;

-- 4. 综合风险评分计算
CREATE PROCEDURE sp_CalculateShopRiskScore
    @shop_id INT,
    @analysis_date DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    IF @analysis_date IS NULL
        SET @analysis_date = GETDATE();
    
    DECLARE @total_score DECIMAL(5,2) = 0;
    DECLARE @risk_factors NVARCHAR(MAX) = '';
    DECLARE @sales_decline_rate DECIMAL(5,4), @return_rate DECIMAL(5,4), @avg_anomaly_rate DECIMAL(5,4);
    
    -- 计算各项指标
    EXEC sp_CalculateSalesDeclineRate @shop_id, @analysis_date;
    SELECT @sales_decline_rate = decline_rate FROM #temp_sales_decline;
    
    EXEC sp_CalculateReturnRate @shop_id, @analysis_date;
    SELECT @return_rate = return_rate FROM #temp_return_rate;
    
    EXEC sp_CalculateAvgOrderValueAnomaly @shop_id, @analysis_date;
    SELECT @avg_anomaly_rate = anomaly_rate FROM #temp_avg_anomaly;
    
    -- 计算加权风险评分
    SELECT @total_score = 
        ISNULL(@sales_decline_rate, 0) * 0.25 +
        ISNULL(@return_rate, 0) * 0.20 +
        ISNULL(@avg_anomaly_rate, 0) * 0.15;
    
    -- 构建风险因素描述
    SET @risk_factors = '{"factors": [';
    IF @sales_decline_rate > 0.15
        SET @risk_factors = @risk_factors + '{"factor": "销售下滑", "value": "' + CAST(@sales_decline_rate AS NVARCHAR(10)) + '"}';
    
    IF @return_rate > 0.10
        SET @risk_factors = @risk_factors + CASE WHEN @risk_factors != '{"factors": [' THEN ',' ELSE '' END + 
        '{"factor": "退货率异常", "value": "' + CAST(@return_rate AS NVARCHAR(10)) + '"}';
    
    IF @avg_anomaly_rate > 0.30
        SET @risk_factors = @risk_factors + CASE WHEN @risk_factors != '{"factors": [' THEN ',' ELSE '' END + 
        '{"factor": "客单价异常", "value": "' + CAST(@avg_anomaly_rate AS NVARCHAR(10)) + '"}';
    
    SET @risk_factors = @risk_factors + ']}';
    
    -- 确定风险等级
    DECLARE @risk_level NVARCHAR(20);
    IF @total_score >= 0.3
        SET @risk_level = 'HIGH';
    ELSE IF @total_score >= 0.15
        SET @risk_level = 'MEDIUM';
    ELSE
        SET @risk_level = 'LOW';
    
    -- 插入或更新风险评分
    MERGE Shop_Risk_Score AS target
    USING (SELECT @shop_id, @analysis_date, @total_score, @risk_level, @risk_factors) AS source
    (shop_id, analysis_date, total_risk_score, risk_level, risk_factors)
    ON target.shop_id = source.shop_id AND target.analysis_date = source.analysis_date
    WHEN MATCHED THEN
        UPDATE SET 
            total_risk_score = source.total_risk_score,
            risk_level = source.risk_level,
            risk_factors = source.risk_factors,
            created_date = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (shop_id, analysis_date, total_risk_score, risk_level, risk_factors)
        VALUES (source.shop_id, source.analysis_date, source.total_risk_score, source.risk_level, source.risk_factors);
    
    SELECT @total_score AS total_risk_score, @risk_level AS risk_level, @risk_factors AS risk_factors;
END;

-- 5. 风险预警生成
CREATE PROCEDURE sp_GenerateRiskAlerts
    @analysis_date DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    IF @analysis_date IS NULL
        SET @analysis_date = GETDATE();
    
    -- 生成高风险店铺预警
    INSERT INTO Risk_Alert (shop_id, alert_type, alert_level, alert_message)
    SELECT 
        srs.shop_id,
        'HIGH_RISK_SCORE',
        'HIGH',
        '店铺风险评分过高：' + CAST(srs.total_risk_score AS NVARCHAR(10)) + '，建议立即关注'
    FROM Shop_Risk_Score srs
    WHERE srs.analysis_date = @analysis_date
    AND srs.risk_level = 'HIGH'
    AND NOT EXISTS (
        SELECT 1 FROM Risk_Alert ra 
        WHERE ra.shop_id = srs.shop_id 
        AND ra.alert_type = 'HIGH_RISK_SCORE'
        AND ra.alert_date = @analysis_date
    );
    
    -- 生成销售下滑预警
    INSERT INTO Risk_Alert (shop_id, alert_type, alert_level, alert_message)
    SELECT 
        srs.shop_id,
        'SALES_DECLINE',
        'MEDIUM',
        '销售下滑率超过15%，需要关注销售策略'
    FROM Shop_Risk_Score srs
    WHERE srs.analysis_date = @analysis_date
    AND srs.risk_factors LIKE '%销售下滑%'
    AND NOT EXISTS (
        SELECT 1 FROM Risk_Alert ra 
        WHERE ra.shop_id = srs.shop_id 
        AND ra.alert_type = 'SALES_DECLINE'
        AND ra.alert_date = @analysis_date
    );
END; 